﻿using AutoMapper.Execution;
using Microsoft.Extensions.DependencyInjection;
using PetaPoco;
using PmSoft;
using PmSoft.ApplicationContext;
using PmSoft.Caching;
using PmSoft.DBContext;
using PmSoft.Repositories;

namespace Modules.CheckIn.Repositories
{
    [UseDI(ServiceLifetime.Scoped)]
    public class CheckinWorkHoursRepository : Repository<CheckinWorkHours, CheckinDbContext>
    {
        public CheckinWorkHoursRepository(CheckinDbContext dbContext, IApplicationContext applicationContext) : base(dbContext, applicationContext)
        {
        }

        /// <summary>
        /// 获取企业在项目下指定月份总计工天
        /// </summary>
        /// <param name="month">月份</param>
        /// <param name="projectId">项目ID</param>
        /// <param name="corpId">企业ID</param>
        /// <returns></returns>
        public int GetTotalCheckinDays(int month, int projectId, int corpId)
        {
            Sql sql = Sql.Builder
                 .Select("IFNULL(SUM(checkin_day),0)")
                 .From("checkin_work_hours")
                 .Where("project_id=@0 AND corp_id=@1", projectId, corpId);
            return DbContext.ExecuteScalar<int>(sql);
        }

        /// <summary>
        /// 更新或创建
        /// </summary>
        /// <param name="entity"></param>
        public void CreateOrUpdate(CheckinWorkHours entity)
        {
            Sql sql = Sql.Builder
                 .Select("id")
                 .From("checkin_work_hours")
                 .Where("member_id=@0 AND month=@1", entity.MemberId, entity.Month);
            var id = DbContext.FirstOrDefault<int>(sql);
            if (id > 0)
            {
                entity.Id = id;
                base.Update(entity);
            }
            else
                base.Insert(entity);
        }


        /// <summary>
        /// 获取指定月份今日未更新统计的成员ID集合
        /// </summary>
        /// <param name="date"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public IEnumerable<int> GetMemberIdsInNoUpdate(DateTime month, int limit = 100)
        {
            Sql sql = Sql.Builder.Select("mcp.object_id")
                .From("member_checkin_permonth AS mcp")
                .Where("mcp.year=@0 AND mcp.month=@1", month.Year, month.Month)
                .Where("NOT EXISTS (SELECT 0 FROM checkin_work_hours AS ws WHERE mcp.object_id=ws.member_id AND ws.month=@0 AND  DATEDIFF(ws.last_update_time,@1)=0)"
                , month.ToString("yyyyMM"), DateTime.Now)

                .Append($"LIMIT {limit}");
            return DbContext.FetchFirstColumn<int>(sql);
        }


        /// <summary>
        /// 获取班组成员所有月份考勤工时统计集合
        /// </summary>
        /// <param name="memberId"></param>
        /// <returns></returns>
        public IEnumerable<CheckinWorkHours> GetCheckinWorkHours(int memberId)
        {
            int areaVersion = EntityCacheService.GetAreaVersion("MemberId", memberId);
            return GetTopEntities(1000, CachingExpirationType.ObjectCollection, () =>
            {
                return $"CheckinWorkHours:{memberId}:{areaVersion}";
            }, () =>
            {
                return Sql.Builder.Select("id").From("checkin_work_hours").Where("member_id=@0", memberId);
            });
        }

        public IEnumerable<CheckinWorkHours> GetCheckinWorkHoursByMonthAndMemberIds(int month, IEnumerable<int> memberIds)
        {
            if (month == null || !memberIds.Any())
                return Enumerable.Empty<CheckinWorkHours>();
            Sql sql = Sql.Builder.Select("id").From("checkin_work_hours").Where("month=@0 AND member_id in(@1)", month, memberIds);
            var ids = DbContext.FetchFirstColumn<int>(sql);

            return PopulateEntitiesByEntityIds(ids);
        }

        public IEnumerable<CheckinWorkHours> GetCheckinWorkHoursByMonthsAndMemberIds(IEnumerable<int> month, IEnumerable<int> memberIds, IEnumerable<int> projectIds)
        {
            if (month == null || !memberIds.Any())
                return Enumerable.Empty<CheckinWorkHours>();
            Sql sql = Sql.Builder.Select("id").From("checkin_work_hours").Where("month in(@0) AND member_id in(@1) and project_id in(@2)", month, memberIds, projectIds);
            var ids = DbContext.FetchFirstColumn<int>(sql);

            return PopulateEntitiesByEntityIds(ids);
        }

        public IEnumerable<CheckinWorkHours> GetCheckinWorks(int month, IEnumerable<int> memberIds)
        {
            Sql sql = Sql.Builder.Select("id").From("checkin_work_hours").Where("month=@0 AND member_id IN(@1)",  month, memberIds);
            var ids = DbContext.FetchFirstColumn<int>(sql);

            return PopulateEntitiesByEntityIds(ids);
        }

        //public IEnumerable<CheckinWorkHours> GetCheckinWorkHoursByProjectIdsAndMonth(IEnumerable<int> projectIds, string month)
        //{
        //    if (projectIds == null || !projectIds.Any())
        //        return Enumerable.Empty<CheckinWorkHours>();
        //    Sql sql = Sql.Builder.Select("id").From("checkin_work_hours").Where("project_id in(@0) AND month=@1", projectIds, month);
        //    var ids = DbContext.FetchFirstColumn<int>(sql);

        //    return PopulateEntitiesByEntityIds(ids);
        //}
    }
}